use SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdAccountDetails')
	BEGIN
		DROP  Procedure  dbo.InsUpdAccountDetails
	END
GO

CREATE Procedure [dbo].[InsUpdAccountDetails] (
	@ACCOUNTNO				VARCHAR(10)
,	@ACCOUNTGUID			VARCHAR(100)	
,	@ACCOUNTNAME			VARCHAR(100)
,	@NOOFLOCATIONS			VARCHAR(10)	 = '0'
,	@ACCOUNTTYPEID			VARCHAR(10)	 = ''
,	@STREET1				VARCHAR(100) = ''
,	@STREET2				VARCHAR(100) = ''
,	@CITY					VARCHAR(100) = ''
,	@STATE					VARCHAR(100) = ''
,	@ZIP					VARCHAR(100) = ''
,	@PHONE					VARCHAR(100) = ''
,	@FAX					VARCHAR(100) = ''
,	@WEBSITE				VARCHAR(100) = ''
,	@VERTICALID				VARCHAR(10)	 = NULL
,	@REFERRALSOURCEID		VARCHAR(10)	 = '1'
,	@NOTEID					VARCHAR(10)	 = ''
,	@NOTES					VARCHAR(max) = ''
,	@ISACTIVE				VARCHAR(10)	 = '1'
,	@USERID					VARCHAR(10)	 = '1'
,	@ADDRESSNO				VARCHAR(10)	 = ''
,	@ADDRESSGUID			VARCHAR(100) = ''
,	@COMMUNICATIONID		VARCHAR(10)	 = ''

-- BELOW FIELDS GOES INTO TR_RELATIONSHIP TABLE
,	@PARENTACCOUNTNO		VARCHAR(10)		= '0'
,	@OWNERID				VARCHAR(10)	 = '1'
,	@PRIMARYCONTACTID		VARCHAR(10)	 = ''
,	@RESELLERID				VARCHAR(10)	 = NULL
)
AS
BEGIN

	-- ***********************************************
	-- BELOW PARAMETERS ARE CURRENTLY HARDCODED
	-- START: DONOT MODIFY THE BELOW CODE
	-- ***********************************************
	DECLARE
		@PARTNERDIVISIONID		VARCHAR(10)
	,	@ACCOUNTGROUP			VARCHAR(10)
	,	@DATAORIGIN				VARCHAR(10)
	,	@ADDRESSTYPE			VARCHAR(10)
	,	@PHONEEXTENSION			VARCHAR(10)
	,	@CELLPHONE				VARCHAR(10)
	,	@NOTETYPE				VARCHAR(10)

	SELECT
		@PARTNERDIVISIONID		= '1'
	,	@ACCOUNTGROUP			= '1'
	,	@DATAORIGIN				= '1'
	,	@ADDRESSTYPE			= '2'
	,	@PHONEEXTENSION			= ''
	,	@CELLPHONE				= ''
	,	@NOTETYPE				= '3'
	-- ***********************************************
	-- END: DONOT MODIFY THE BELOW CODE
	-- ***********************************************


	DECLARE @C_PARTNERCATEGORY	INT
	DECLARE @C_PARTNERTYPE		INT
	
	SET @C_PARTNERCATEGORY	= 2
	SET @C_PARTNERTYPE		= 2

	SET @ISACTIVE = CASE WHEN @ISACTIVE IN ('X','1') THEN '1' ELSE '' END
	
	BEGIN TRAN
		BEGIN TRY
			IF EXISTS(SELECT 1 FROM TR_PARTNER WHERE PartnerGUID = @ACCOUNTGUID) 
			BEGIN		-- UPDATE
				UPDATE TR_PARTNER
				SET	PartnerName			= @ACCOUNTNAME
				,	PartnerDivision		= @PARTNERDIVISIONID
				,	AccountGroup		= @ACCOUNTTYPEID
				,	DataOrigin			= @REFERRALSOURCEID
				,	Vertical			= @VERTICALID
				,	isActive			= @ISACTIVE
				,	ChangedBy			= @USERID
				,	ChangeDate			= GETDATE()
				,	Website				= @WEBSITE
				,	NoOfLocations		= @NOOFLOCATIONS
				WHERE
					PartnerNo			= @ACCOUNTNO
				AND	PartnerGuid			= @ACCOUNTGUID
				AND	PartnerCategory		= @C_PARTNERCATEGORY
				AND	PartnerType			= @C_PARTNERTYPE

				IF ( ISNULL(@ADDRESSGUID,'') = '')
					SELECT @ADDRESSGUID	= CAST(NEWID() AS VARCHAR(100))

			END
			ELSE
			BEGIN		-- INSERT
				SELECT @ACCOUNTNO	= ISNULL(MAX(PartnerNo), 0) +1 FROM TR_PARTNER
				SELECT @ADDRESSGUID		= CAST(NEWID() AS VARCHAR(100))

				-- PARTNER

				INSERT INTO TR_PARTNER (PartnerNo, PartnerGUID, PartnerCategory, PartnerType, PartnerName, 
										PartnerDivision, AccountGroup, DataOrigin, Vertical, isActive, 
										WebSite, NoOfLocations,
										CreatedBy, CreateDate, ChangedBy, ChangeDate)
				VALUES(
					@ACCOUNTNO				-- PartnerNo
				,	@ACCOUNTGUID			-- PartnerGUID
				,	@C_PARTNERCATEGORY		-- PartnerCategory
				,	@C_PARTNERTYPE			-- PartnerType
				,	@ACCOUNTNAME			-- PartnerName
				,	@PARTNERDIVISIONID		-- PartnerDivision
				,	@ACCOUNTTYPEID			-- AccountGroup
				,	@REFERRALSOURCEID		-- DataOrigin
				,	@VERTICALID				-- Vertical
				,	@ISACTIVE				-- isActive
				,	@WEBSITE				-- WEBSITE
				,	@NOOFLOCATIONS			-- NoOfLocations
				,	@USERID					-- CreatedBy
				,	GetDate()				-- CreateDate
				,	@USERID					-- ChangedBy
				,	GetDate()				-- ChangeDate
				)
			END

			exec InsUpdAddress 
				@ADDRESSGUID	= @ADDRESSGUID
			,	@PARTNERGUID	= @ACCOUNTGUID
			,	@ADDRESSNO		= @ADDRESSNO
			,	@STREET1		= @STREET1
			,	@STREET2		= @STREET2
			,	@CITY			= @CITY
			,	@STATE			= @STATE
			,	@ZIP			= @ZIP
			,	@USERID			= @USERID

			exec InsUpdCommunication
				@ADDRESSGUID		= @ADDRESSGUID
			,	@PARTNERGUID		= @ACCOUNTGUID
			,	@COMMUNICATIONID	= @COMMUNICATIONID
			,	@PHONE				= @PHONE
			,	@PHONEEXTENSION		= @PHONEEXTENSION
			,	@FAX				= @FAX
			,	@CELLPHONE			= @CELLPHONE
			,	@USERID				= @USERID

			exec InsUpdNotes
				@NOTEID				= @NOTEID
			,	@NOTESOURCEGUID		= @ACCOUNTGUID
			,	@SOURCEID			= @ACCOUNTNO
			,	@NOTE				= @NOTES
			,	@USERID				= @USERID
			,	@NOTETYPE			= @NOTETYPE


			IF ( CAST(ISNULL(@PARENTACCOUNTNO,'0') AS INT) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @ACCOUNTGUID
				,	@PARTNERID	= @PARENTACCOUNTNO
				,	@RELATIONSHIPTYPE = 7 
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END

			IF ( CAST(ISNULL(@OWNERID,'0') AS INT) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @ACCOUNTGUID
				,	@PARTNERID	= @OWNERID
				,	@RELATIONSHIPTYPE = 3 
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END

			IF ( CAST(ISNULL(@PRIMARYCONTACTID,'0') AS INT ) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @ACCOUNTGUID
				,	@PARTNERID	= @PRIMARYCONTACTID
				,	@RELATIONSHIPTYPE = 2 
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END

			IF ( CAST(ISNULL(@RESELLERID,'0') AS INT ) > 0)
			BEGIN
				exec InsUpdRelationships
					@SOURCEGUID	= @ACCOUNTGUID
				,	@PARTNERID	= @RESELLERID
				,	@RELATIONSHIPTYPE = 1 
				,	@ISMAIN		= 1
				,	@USERID		= @USERID
			END

			COMMIT TRAN

			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>S</RESULT>
						<MESSAGE>Account Details saved successfully</MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to save Account Details. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
GO
